using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace QLVMBAYDatacess
{
    public abstract class DataAccessBase
    {
        protected static readonly DbProviderFactory factory;
        private DbTransaction Transaction = null;
        private DbConnection _connection;
        public IsolationLevel IsolationLevel = IsolationLevel.ReadUncommitted;
        public bool UseCustomTransaction = false;
        protected string _insertSql = null;
        protected string _updateSql = null;
        protected string _deleteSql = null;
        protected string _selectSql = null;
        protected string _primarSql = null;
        public void CommitTransaction()
        {
            if (UseCustomTransaction)
                Transaction.Commit();
        }
        public void RollbackTransaction()
        {
            if (UseCustomTransaction)
                Transaction.Rollback();
        }

        static DataAccessBase()
        {

            string providerName = ConfigurationManager.ConnectionStrings["QLVMBAY.ConnectionString"].ProviderName;
            factory = DbProviderFactories.GetFactory(providerName);
        }
        protected DataTable Select(string sql, string path)
        {

            using (DbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter())
            {
                using(DbCommand cmd = ConnectionDBF(path).CreateCommand())
                {
                    cmd.CommandText = sql;
                    da.SelectCommand = cmd;
                    DataTable tb = new DataTable();

                    try
                    {
                        da.Fill(tb);
                        ConnectionDBF(path).Close();
                    }
                    catch 
                    {

                    }
                    return tb;  
                }
                    
                
            }

        }

        private DbConnection _connect= new System.Data.OleDb.OleDbConnection();
       
        private static string connectString = "";
        protected DbConnection ConnectionDBF(string path)
        {
            connectString = "Provider=vfpoledb.1;Data Source=" + path + "";
            if (string.IsNullOrEmpty(_connect.ConnectionString))
            {
                _connect.ConnectionString = connectString;
            }
            if (_connect.State == ConnectionState.Closed)
            {
                _connect.Open();
            }
            return _connect;
        }
        protected DbConnection Connection
        {
            get
            {
                if (_connection == null || _connection.ConnectionString == string.Empty)
                {
                    _connection = factory.CreateConnection();
                    string connStr = ConfigurationManager.ConnectionStrings["QLVMBAY.ConnectionString"].ConnectionString;//Properties.Settings.Default.ConnectionString;
                    _connection.ConnectionString = connStr;
                }
                if (_connection.State == ConnectionState.Closed)
                {
                    _connection.Open();
                }
                ////
                if (UseCustomTransaction)
                    Transaction = _connection.BeginTransaction(IsolationLevel);
                return _connection;
            }
        }
        protected DbCommand GetCommand(string sql)
        {
            DbCommand comm = factory.CreateCommand();
            comm.Connection = Connection;
            if (UseCustomTransaction)
                comm.Transaction = Transaction;

            comm.CommandText = sql;
            comm.CommandType = CommandType.Text;
            return comm;
        }
        protected static void SetParam(DbCommand comm, object pramValue)
        {
            DbParameter pram = comm.CreateParameter();
            pram.Value = pramValue;
            comm.Parameters.Add(pram);
        }
        protected static void SetParam(DbCommand comm, string paramName, object pramValue)
        {
            DbParameter pram = comm.CreateParameter();
            pram.Value = pramValue;
            pram.ParameterName = paramName;
            comm.Parameters.Add(pram);
        }
        protected static void SetParam(SqlCommand comm, string paramName, object pramValue)
        {
            SqlParameter pram = comm.CreateParameter();
            pram.Value = pramValue;
            pram.ParameterName = paramName;
            comm.Parameters.Add(pram);
        }
        protected static void SetParam(DbCommand comm, string paramName)
        {
            DbParameter pram = comm.CreateParameter();
            pram.Value = null;
            pram.ParameterName = paramName;
            comm.Parameters.Add(pram);
        }
        protected DataTable SelectData(string sql, params DbParameter[] ps)
        {

            DbDataAdapter da = factory.CreateDataAdapter();
            DbCommand cmd = factory.CreateCommand();
            DbConnection cn = factory.CreateConnection();

            string connStr = ConfigurationManager.ConnectionStrings["QLVMBAY.ConnectionString"].ConnectionString;//Properties.Settings.Default.ConnectionString;
            cn.ConnectionString = connStr;

            cmd.Connection = cn;
            cmd.CommandText = sql;
            da.SelectCommand = cmd;

            if (ps.Length > 0)
            {
                cmd.Parameters.AddRange(ps);
            }
            DataTable tb = new DataTable();
            try
            {
                da.Fill(tb);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return tb;
        }

        protected DbParameter GetParam(string paramName, object paramValue)
        {
            DbParameter pram = factory.CreateParameter();
            pram.Value = paramValue;
            pram.ParameterName = paramName;
            return pram;
        }
    }
}

